package com.loongtech.bi.advertisment.service.impl;

import com.loongtech.bi.advertisment.model.ActiveTranData;
import com.loongtech.bi.advertisment.model.NewRemainData;
import com.loongtech.bi.advertisment.service.AdvDayDataAllService;
import com.loongtech.bi.dao.CommonDao;
import com.loongtech.bi.entity.pay.EntityNewUserLTV;
import com.loongtech.core.util.Time;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service("advDayDataAllService")
public class AdvDayDataAllServiceImpl implements AdvDayDataAllService {

    @Resource(name = "commonDao")
    private CommonDao commonDao;

    @Override
    public List<ActiveTranData> getActiveTranDatas(String startday, String endday, String[] selectedAffiliation,
                                                   String[] selectedChannelName, String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype,
                                                   String[] selectedAdvPosition) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.dateStr,a.clkEquipment, ");
        sql.append("a.actEquipment,a.newActive,a.newEquipment,a.newAccount,a.newPay,a.newPayUser, ");
        sql.append("case when a.newAccount=null then 0 else round(a.newPayUser/a.newAccount,2) end as payrate,round(c.discount) discount , ");
        sql.append("case when a.newAccount=null then 0 else round(c.discount/a.newAccount,2) end as cpa,b.totalpay, d.num, round(d.num/a.newAccount,2) as tpr,");
        sql.append("case when c.discount=null then 0 else round(b.roi/c.discount,4) * 100 end as roi  from (  ");
        sql.append("select dateStr , sum(clkEquipment)clkEquipment,sum(actEquipment)actEquipment,sum(newActive)newActive ");
        sql.append(",sum(newEquipment)newEquipment,sum(newAccount)newAccount,round(sum(newPay)/100) as newPay,sum(newPayUser)newPayUser from (");
        sql.append("select ifnull(a.logdate,'')  as dateStr, ");
        sql.append("ifnull(tdclick,0) clkEquipment,ifnull(tdactivate,0) actEquipment, ");
        sql.append("ifnull(newactive,0) newActive , ifnull(newmac,0) newEquipment, ifnull(newuser,0) newAccount,ifnull(paymoney,0) newPay, ifnull(payuser,0) newPayUser  ");
        sql.append("from td_adv_active a ");
        sql.append("left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where  a.logdate >=? and a.logdate <=?  ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ")");
        }
        sql.append(")a group by dateStr)a left join (  ");

        sql.append("select a.dateStr , sum(a.totalpay)totalpay , sum(a.roi)roi from(  ");
        sql.append("select a.dateStr , totalpay totalpay,   ");
        sql.append("case when bi.roi is not null then round(totalpay*bi.roi,2)   ");
        sql.append("when bi.roi is null &&  a.platform = 'ios' then round(totalpay*de.iosRoi,2)  ");
        sql.append("when bi.roi is null &&  a.platform = '安卓' then round(totalpay*de.androidRoi,2)   ");
        sql.append("else  0  end  as roi  ");
        sql.append("from (  ");
        sql.append("select ifnull(a.createdate,'')  as dateStr,    ");
        sql.append("ifnull(bi.channelName,a.url) channelName,ifnull(bi.agent,a.url) agent ,ifnull(bi.platform,a.url) platform , ifnull(sum(totalpay),0) totalpay  ");
        sql.append("from td_adv_totalpay a    ");
        sql.append("left join bi_advconfig bi on a.url = bi.id    ");
        sql.append("where a.createdate >=? and a.createdate <=?  ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ")");
        }
        sql.append(" group by a.createdate,bi.channelName,bi.agent,bi.platform)a ");
        sql.append(" left join bi_advcost bi on  a.platform = bi.platform and a.agent = bi.agent and a.channelName = bi.channelName  and a.dateStr = bi.ldate  ");
        sql.append(" left join bi_advdefault de on 1 = 1)a ");
        sql.append(" group by a.dateStr ");
        sql.append(" )b on a.dateStr = b.dateStr  ");
        sql.append("left join (select ifnull(a.ldate,'')  as dateStr,   ");
        sql.append("ifnull(sum(distinct discount),0) discount   ");
        sql.append("from bi_advcost a  ");
        sql.append("left join bi_advconfig bi on a.platform = bi.platform and a.agent = bi.agent and a.channelName = bi.channelName  ");
        sql.append("where a.ldate >=? and a.ldate <=?  ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and bi.affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and bi.channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and bi.agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and bi.platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and bi.paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and bi.advPosition in (" + getParams(selectedAdvPosition) + ")");
        }
        sql.append(" group by a.ldate)c on a.dateStr = c.dateStr  ");
        sql.append(" left join (select ifnull(a.createdate,'')  as dateStr,   ifnull(sum(num),0) num   from td_adv_totalpayuser a  left join bi_advconfig bi on a.url = bi.id ");
        sql.append("where a.createdate >=? and a.createdate <=?  ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and bi.affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and bi.channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and bi.agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and bi.platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and bi.paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and bi.advPosition in (" + getParams(selectedAdvPosition) + ")");
        }
        sql.append(" group by a.createdate)d on a.dateStr = d.dateStr  ");
        sql.append(" order by a.dateStr desc ");
        System.out.println("@@@@@@sql is : " + sql);
        return commonDao.queryObjList(sql.toString(), new Object[]{startday, endday, startday, endday, startday,
                endday,startday, endday}, ActiveTranData.class);
    }

    @Override
    public int getAllRoi(String startday, String endday, String[] selectedAffiliation, String[] selectedChannelName,
                         String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype, String[] selectedAdvPosition)
            throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select ifnull(sum(round(totalpay*   ");
        sql.append("case when bi.roi is null && a.platform = 'ios' then def.iosRoi   ");
        sql.append("when bi.roi is null && a.platform = '安卓' then def.androidRoi   ");
        sql.append("else bi.roi end,2)),0)  roi from (   ");
        sql.append("select ifnull(a.createdate,'')  as dateStr,   ");
        sql.append("ifnull(bi.channelName,a.url) channelName,ifnull(bi.agent,a.url) agent ,ifnull(bi.platform,a.url) platform , ifnull(sum(totalpay),0) totalpay   ");
        sql.append("from td_adv_totalpay a   ");
        sql.append("left join bi_advconfig bi on a.url = bi.id     ");
        sql.append("where a.createdate >=? and a.createdate <=?   ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ")");
        }
        sql.append(" group by a.createdate,bi.channelName,bi.agent,bi.platform)a    ");
        sql.append(" left join bi_advcost bi on  a.platform = bi.platform and a.agent = bi.agent and a.channelName = bi.channelName  and a.dateStr = bi.ldate  ");
        sql.append(" left join bi_advdefault def on 1=1  ");
        return commonDao.queryForInt(sql.toString(), new Object[]{startday, endday});
    }

    @Override
    public List<NewRemainData> getNewRemainDatas(String startday, String endday, String[] selectedAffiliation, String[] selectedChannelName, String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype, String[] selectedAdvPosition) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select  ifnull(createdate,'') dateStr, ifnull(max(newuser),0) newAccount  ");
        sql.append(getSelRemainParams("remainnum", "remain"));
        sql.append("from ( ");
        sql.append("select a.logdate, a.createdate , a.channelName, a.agent,remainnum,newuser from (  ");
        sql.append("select t.logdate, createdate , channelName, agent , sum(t.remainnum)remainnum   ");
        sql.append("from td_adv_newremain t    ");
        sql.append("inner join bi_advconfig bi on t.url = bi.id   ");
        sql.append("where t.createdate >= ? and t.createdate <=? ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ") ");
        }
        sql.append("GROUP BY t.logdate,createdate)a inner join ( ");
        sql.append("select t.logdate,sum(newuser)newuser ");
        sql.append("from td_adv_active t   ");
        sql.append("inner join bi_advconfig bi on t.url = bi.id  ");
        sql.append("where t.logdate >= ? and t.logdate <=? ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ") ");
        }
        sql.append("GROUP BY t.logdate)b ");
        sql.append("on a.createdate = b.logdate)as a  ");
        sql.append("group by createdate ");
        return commonDao.queryObjList(sql.toString(), new Object[] { startday, endday, startday, endday },
                NewRemainData.class);
    }

    @Override
    public List<List<String>> getTotalPayDatas(String startday, String endday, String[] selectedAffiliation, String[] selectedChannelName, String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype, String[] selectedAdvPosition) throws Exception {

        StringBuilder sql = new StringBuilder();
        sql.append("select  createdate ,ifnull(max(newuser),0) newuser  ");
        sql.append(getSelPayParams("totalpay", "income"));
        sql.append("from(  ");
        sql.append("select a.logdate, a.createdate , totalpay,newuser from (  ");
        sql.append("select t.logdate, createdate , sum(t.totalpay)totalpay   ");
        sql.append("from td_adv_totalpay t    ");
        sql.append("inner join bi_advconfig bi on t.url = bi.id   ");
        sql.append("where t.createdate >= ? and t.createdate <=? ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ") ");
        }
        sql.append("GROUP BY t.logdate,createdate)a left join ( ");
        sql.append("select t.logdate,sum(newuser)newuser ");
        sql.append("from td_adv_active t   ");
        sql.append("inner join bi_advconfig bi on t.url = bi.id  ");
        sql.append("where t.logdate >= ? and t.logdate <=? ");
        if (selectedAffiliation != null && selectedAffiliation.length != 0) {
            sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
        }
        if (selectedChannelName != null && selectedChannelName.length != 0) {
            sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
        }
        if (selectedAgent != null && selectedAgent.length != 0) {
            sql.append("and agent in (" + getParams(selectedAgent) + ")");
        }
        if (selectedPlatform != null && selectedPlatform.length != 0) {
            sql.append("and platform in (" + getParams(selectedPlatform) + ")");
        }
        if (selectedPaytype != null && selectedPaytype.length != 0) {
            sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
        }
        if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
            sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ") ");
        }
        sql.append("GROUP BY t.logdate)b ");
        sql.append("on a.createdate = b.logdate)as a ");
        sql.append("group by createdate order by createdate desc ");

        List<List<String>> re = commonDao.queryListData(sql.toString(), new Object[] { startday, endday, startday,
                endday });
        for (List<String> ls : re) {
            for (int i = 3; i < ls.size(); i++) {
                if (!"-1".equals(ls.get(i)))
                    ls.set(i, String.valueOf(Integer.valueOf(ls.get(i)) + Integer.valueOf(ls.get(i - 1))));
            }
        }
        return re;
    }

    @Override
    public List<EntityNewUserLTV> getLTVPay(String startday, String endday) throws Exception {
        return commonDao.queryObjList("select * from td_adv_newuserltv where 1=1 and startday>=? and startday<=? and endday<=? order by startday", new Object[] { startday, endday, Time.formatTime(Time.formatDateStr(endday, Time.TIMEPATTERN_SIMPLE) + 179 * Time.DAY,Time.TIMEPATTERN_SIMPLE)}, EntityNewUserLTV.class);
    }

    private String getSelRemainParams(String name, String paramName) throws Exception {
        StringBuilder sb = new StringBuilder();
        for (int i = 1; i < 90; i++) {
            sb.append(",if(date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval "
                    + i
                    + " day)>=curdate(),'-',sum(if(logdate=DATE_FORMAT(date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval "
                    + i + " day),'%Y-%m-%d'), round(" + name + "/newuser,4),''))+'') " + paramName + (i + 1) + "  ");
        }
        return sb.toString();
    }

    private String getSelPayParams(String name, String paramName) throws Exception {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < 90; i++) {
            sb.append(",if(date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval "
                    + i
                    + " day)>=curdate(),-1,sum(if(logdate=DATE_FORMAT(date_add(STR_TO_DATE(createdate,'%Y-%m-%d'),interval "
                    + i + " day),'%Y-%m-%d'), " + name + ",0))) " + paramName + (i + 1) + "   ");
        }
        return sb.toString();
    }

    private String getParams(String[] param) throws Exception {
        StringBuilder s = new StringBuilder();
        for (String str : param) {
            s.append("'" + str + "',");
        }
        return s.substring(0, s.length() - 1);
    }
}
